package com.situ.system.department.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.situ.system.department.model.DepartmentModel;
import com.situ.system.util.JDBCUtil;
import com.situ.system.util.MD5;

public class DepartmentDao {
	private Connection connection = null;
	private PreparedStatement ps = null;

	// 增加
	public String insert(DepartmentModel model) {
		String sql = "insert into department(d_id, d_name, d_tel) value(?,?,?)";
		return "" + JDBCUtil.shuai(sql, model.getId(), model.getName(), model.getTel());
	}

	// 删除
	public String delete(DepartmentModel model) {
		String sql = "delete from department where d_id = ?";
		return "" + JDBCUtil.shuai(sql, model.getId());
	}

	// 修改
	public String update(DepartmentModel model) {
		String sql = "update department set d_name = ?, d_tel = ? where d_id = ?";
		return "" + JDBCUtil.shuai(sql, model.getName(), model.getTel(), model.getId());
	}

	// 查询
	public List<DepartmentModel> select(DepartmentModel model) {
		StringBuffer sql = new StringBuffer(
				"select d_id, d_name, d_tel,(select count(1) from work.employee where d_id = department.d_id)count from work.department where 1=1");
		List<Object> list = new ArrayList<Object>();
		list = where(sql, model);
		List<DepartmentModel> list2 = new ArrayList<DepartmentModel>();
		ResultSet set = null;
		try {
			connection = JDBCUtil.getConnection();
			ps = connection.prepareStatement(sql.toString());
			for (int i = 0; i < list.size(); i++) {
				ps.setObject(i + 1, list.get(i));
			}
			set = ps.executeQuery();
			while (set.next()) {
				DepartmentModel model1 = new DepartmentModel();
				model1.setId(set.getString("d_id"));
				model1.setName(set.getString("d_name"));
				model1.setTel(set.getString("d_tel"));
				model1.setCount(set.getInt("count"));
				list2.add(model1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, ps);
		}
		return list2;
	}

	public Integer selectCount(DepartmentModel model) {
		StringBuffer sql = new StringBuffer("select count(1) from department");
		ResultSet rs = null;
		try {
			connection = JDBCUtil.getConnection();
			ps = connection.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, ps);
		}
		return null;
	}

	public List<Object> where(StringBuffer sql, DepartmentModel model) {
		String s_id = model.getId();
		List<Object> list = new ArrayList<Object>();
		if (s_id != null && !s_id.trim().isEmpty()) {
			sql.append(" and d_id like ?");
			list.add(s_id);
		}
		String name = model.getName();
		if (name != null && !name.trim().isEmpty()) {
			sql.append(" and d_name like ?");
			list.add(name);
		}
		if (model.isPageOn()) {
			sql.append(" limit ?,? ");
			list.add(model.getRowStart());
			list.add(model.getPageLimit());
		}
		return list;
	}
}
